﻿
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Components;
using Model;


namespace MsDAL
{
   public partial  class Sys5a01Base
    {		
		/// <summary>
        /// 增加一条数据
        /// </summary>
        public virtual  SqlApp Add(Model.Sys5a01 sys5a01)
        {
			StringBuilder strSql = new StringBuilder();
			strSql.Append("insert into Sys5a01(");
			strSql.Append("listid ,plistid ,orderid ,colname ,headcn ,coltype ,isquery ,isshow ,oname ,fmt ,editmode ,controltype ,maxlength ,digits ,defvalue ,iskey ,notnull ,columnwidth ,colspan ,columnoption ,columnparam ,tp1 ,tp2 ,tp3 ,tp4 ,tp5 ,remark ,tp6 ,tp7 ,tp8 ,tp9 ,tp10 )");
			strSql.Append(" values (");
			strSql.Append("@listid ,@plistid ,@orderid ,@colname ,@headcn ,@coltype ,@isquery ,@isshow ,@oname ,@fmt ,@editmode ,@controltype ,@maxlength ,@digits ,@defvalue ,@iskey ,@notnull ,@columnwidth ,@colspan ,@columnoption ,@columnparam ,@tp1 ,@tp2 ,@tp3 ,@tp4 ,@tp5 ,@remark ,@tp6 ,@tp7 ,@tp8 ,@tp9 ,@tp10 )");		

			SqlParameter[] parameters = {
				new SqlParameter("@listid",SqlDbType.NVarChar),
				new SqlParameter("@plistid",SqlDbType.NVarChar),
				new SqlParameter("@orderid",SqlDbType.Int),
				new SqlParameter("@colname",SqlDbType.NVarChar),
				new SqlParameter("@headcn",SqlDbType.NVarChar),
				new SqlParameter("@coltype",SqlDbType.NVarChar),
				new SqlParameter("@isquery",SqlDbType.NChar),
				new SqlParameter("@isshow",SqlDbType.NChar),
				new SqlParameter("@oname",SqlDbType.NVarChar),
				new SqlParameter("@fmt",SqlDbType.NVarChar),
				new SqlParameter("@editmode",SqlDbType.NVarChar),
				new SqlParameter("@controltype",SqlDbType.NChar),
				new SqlParameter("@maxlength",SqlDbType.Int),
				new SqlParameter("@digits",SqlDbType.Int),
				new SqlParameter("@defvalue",SqlDbType.NVarChar),
				new SqlParameter("@iskey",SqlDbType.NChar),
				new SqlParameter("@notnull",SqlDbType.NChar),
				new SqlParameter("@columnwidth",SqlDbType.Int),
				new SqlParameter("@colspan",SqlDbType.Int),
				new SqlParameter("@columnoption",SqlDbType.NVarChar),
				new SqlParameter("@columnparam",SqlDbType.NVarChar),
				new SqlParameter("@tp1",SqlDbType.NVarChar),
				new SqlParameter("@tp2",SqlDbType.NVarChar),
				new SqlParameter("@tp3",SqlDbType.NVarChar),
				new SqlParameter("@tp4",SqlDbType.NVarChar),
				new SqlParameter("@tp5",SqlDbType.NVarChar),
				new SqlParameter("@remark",SqlDbType.NVarChar),
				new SqlParameter("@tp6",SqlDbType.NVarChar),
				new SqlParameter("@tp7",SqlDbType.NVarChar),
				new SqlParameter("@tp8",SqlDbType.NVarChar),
				new SqlParameter("@tp9",SqlDbType.NVarChar),
				new SqlParameter("@tp10",SqlDbType.NVarChar)
				};
			parameters[0].Value = sys5a01.Listid;
			parameters[1].Value = sys5a01.Plistid;
			parameters[2].Value = sys5a01.Orderid;
			parameters[3].Value = sys5a01.Colname;
			parameters[4].Value = sys5a01.Headcn;
			parameters[5].Value = sys5a01.Coltype;
			parameters[6].Value = sys5a01.Isquery;
			parameters[7].Value = sys5a01.Isshow;
			parameters[8].Value = sys5a01.Oname;
			parameters[9].Value = sys5a01.Fmt;
			parameters[10].Value = sys5a01.Editmode;
			parameters[11].Value = sys5a01.Controltype;
			parameters[12].Value = sys5a01.Maxlength;
			parameters[13].Value = sys5a01.Digits;
			parameters[14].Value = sys5a01.Defvalue;
			parameters[15].Value = sys5a01.Iskey;
			parameters[16].Value = sys5a01.Notnull;
			parameters[17].Value = sys5a01.Columnwidth;
			parameters[18].Value = sys5a01.Colspan;
			parameters[19].Value = sys5a01.Columnoption;
			parameters[20].Value = sys5a01.Columnparam;
			parameters[21].Value = sys5a01.Tp1;
			parameters[22].Value = sys5a01.Tp2;
			parameters[23].Value = sys5a01.Tp3;
			parameters[24].Value = sys5a01.Tp4;
			parameters[25].Value = sys5a01.Tp5;
			parameters[26].Value = sys5a01.Remark;
			parameters[27].Value = sys5a01.Tp6;
			parameters[28].Value = sys5a01.Tp7;
			parameters[29].Value = sys5a01.Tp8;
			parameters[30].Value = sys5a01.Tp9;
			parameters[31].Value = sys5a01.Tp10;
			
			return new SqlApp(strSql.ToString(),new ArrayList(parameters), false, "数据保存失败！");
        }
	
        /// <summary>
        /// 判断某个值是否存在表中的某个字段
        /// </summary>
        /// <param name="Condition">条件字段名 例:A=@A</param>
        /// <param name="hs">参数和值 如hs["@A"]=123</param>
        /// <returns></returns>
        public virtual bool Exists(String condition, Hashtable hs)
        {
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(1) from Sys5a01 ");
			if (!string.IsNullOrEmpty(condition))
				strSql.Append(" where " + condition);
			List<SqlParameter> list = DBHelper.GetListByHashtable(hs);
			if (list.Count > 0)
				return Convert.ToInt32(DBHelper.ExecuteScalar(strSql.ToString(), list.ToArray())) > 0;
			else
				return Convert.ToInt32(DBHelper.ExecuteScalar(strSql.ToString())) > 0;
        }

		/// <summary>
        /// 更新一条数据
        /// </summary>
        public virtual SqlApp Update(Model.Sys5a01 sys5a01)
        {
			StringBuilder strSql = new StringBuilder();
			strSql.Append("update Sys5a01 set ");
			strSql.Append("plistid=@plistid ,orderid=@orderid ,colname=@colname ,headcn=@headcn ,coltype=@coltype ,isquery=@isquery ,isshow=@isshow ,oname=@oname ,fmt=@fmt ,editmode=@editmode ,controltype=@controltype ,maxlength=@maxlength ,digits=@digits ,defvalue=@defvalue ,iskey=@iskey ,notnull=@notnull ,columnwidth=@columnwidth ,colspan=@colspan ,columnoption=@columnoption ,columnparam=@columnparam ,tp1=@tp1 ,tp2=@tp2 ,tp3=@tp3 ,tp4=@tp4 ,tp5=@tp5 ,remark=@remark ,tp6=@tp6 ,tp7=@tp7 ,tp8=@tp8 ,tp9=@tp9 ,tp10=@tp10 ");
			strSql.Append("where listid=@listid ");
			SqlParameter[] parameters = {
				new SqlParameter("@listid", SqlDbType.NVarChar),
				new SqlParameter("@plistid", SqlDbType.NVarChar),
				new SqlParameter("@orderid", SqlDbType.Int),
				new SqlParameter("@colname", SqlDbType.NVarChar),
				new SqlParameter("@headcn", SqlDbType.NVarChar),
				new SqlParameter("@coltype", SqlDbType.NVarChar),
				new SqlParameter("@isquery", SqlDbType.NChar),
				new SqlParameter("@isshow", SqlDbType.NChar),
				new SqlParameter("@oname", SqlDbType.NVarChar),
				new SqlParameter("@fmt", SqlDbType.NVarChar),
				new SqlParameter("@editmode", SqlDbType.NVarChar),
				new SqlParameter("@controltype", SqlDbType.NChar),
				new SqlParameter("@maxlength", SqlDbType.Int),
				new SqlParameter("@digits", SqlDbType.Int),
				new SqlParameter("@defvalue", SqlDbType.NVarChar),
				new SqlParameter("@iskey", SqlDbType.NChar),
				new SqlParameter("@notnull", SqlDbType.NChar),
				new SqlParameter("@columnwidth", SqlDbType.Int),
				new SqlParameter("@colspan", SqlDbType.Int),
				new SqlParameter("@columnoption", SqlDbType.NVarChar),
				new SqlParameter("@columnparam", SqlDbType.NVarChar),
				new SqlParameter("@tp1", SqlDbType.NVarChar),
				new SqlParameter("@tp2", SqlDbType.NVarChar),
				new SqlParameter("@tp3", SqlDbType.NVarChar),
				new SqlParameter("@tp4", SqlDbType.NVarChar),
				new SqlParameter("@tp5", SqlDbType.NVarChar),
				new SqlParameter("@remark", SqlDbType.NVarChar),
				new SqlParameter("@tp6", SqlDbType.NVarChar),
				new SqlParameter("@tp7", SqlDbType.NVarChar),
				new SqlParameter("@tp8", SqlDbType.NVarChar),
				new SqlParameter("@tp9", SqlDbType.NVarChar),
				new SqlParameter("@tp10", SqlDbType.NVarChar)
				};
			parameters[0].Value = sys5a01.Listid;
			parameters[1].Value = sys5a01.Plistid;
			parameters[2].Value = sys5a01.Orderid;
			parameters[3].Value = sys5a01.Colname;
			parameters[4].Value = sys5a01.Headcn;
			parameters[5].Value = sys5a01.Coltype;
			parameters[6].Value = sys5a01.Isquery;
			parameters[7].Value = sys5a01.Isshow;
			parameters[8].Value = sys5a01.Oname;
			parameters[9].Value = sys5a01.Fmt;
			parameters[10].Value = sys5a01.Editmode;
			parameters[11].Value = sys5a01.Controltype;
			parameters[12].Value = sys5a01.Maxlength;
			parameters[13].Value = sys5a01.Digits;
			parameters[14].Value = sys5a01.Defvalue;
			parameters[15].Value = sys5a01.Iskey;
			parameters[16].Value = sys5a01.Notnull;
			parameters[17].Value = sys5a01.Columnwidth;
			parameters[18].Value = sys5a01.Colspan;
			parameters[19].Value = sys5a01.Columnoption;
			parameters[20].Value = sys5a01.Columnparam;
			parameters[21].Value = sys5a01.Tp1;
			parameters[22].Value = sys5a01.Tp2;
			parameters[23].Value = sys5a01.Tp3;
			parameters[24].Value = sys5a01.Tp4;
			parameters[25].Value = sys5a01.Tp5;
			parameters[26].Value = sys5a01.Remark;
			parameters[27].Value = sys5a01.Tp6;
			parameters[28].Value = sys5a01.Tp7;
			parameters[29].Value = sys5a01.Tp8;
			parameters[30].Value = sys5a01.Tp9;
			parameters[31].Value = sys5a01.Tp10;
	
			return new SqlApp(strSql.ToString(),new ArrayList(parameters), false, "数据保存失败！");			
		}
		
		public virtual SqlApp Delete(Model.Sys5a01 sys5a01)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("DELETE FROM  Sys5a01  ");
			strSql.Append("WHERE listid=@listid ");
			SqlParameter[] parameters = {
				new SqlParameter("@listid", SqlDbType.NVarChar)
				};
			parameters[0].Value = sys5a01.Listid;
	
			return new SqlApp(strSql.ToString(),new ArrayList(parameters), false, "数据保存失败！");		
		}
		
		/// <summary>
        /// 根据Listid得到整个 Sys5a01对象,如果没查到，返回null
        /// </summary>
        /// <param name="Listid">该实体主键 Listid</param>
        /// <returns>实体Sys5a01的对象</returns>
		public virtual Model.Sys5a01 GetModelById(string Listid)
        {
			string where = "listid=@listid ";
			
			string sql = GetSql(where,null);
			
			List<SqlParameter> parameters = new List<SqlParameter>();
			
			SqlParameter p0 = new SqlParameter("@Listid",SqlDbType.NVarChar);
			p0.Value = Listid;
			parameters.Add(p0);
			
			DataTable dt = DBHelper.GetData(sql, parameters.ToArray());
			
			//调用实体Sys5a01的GetSys5a01ListByDataTable方法得到实体对象
			List<Model.Sys5a01> list =Model.Sys5a01.GetSys5a01ListByDataTable(dt);
			
			return  (list.Count>0) ? list[0] : null;
        }
		
		
		 /// <summary>
        /// 得到Sql语句
        /// </summary>
        /// <param name="Condition">Sql语句的条件</param>
		/// <param name="OrderBy">排序条件，默认为String.Empty</param>
        /// <returns></returns>
        protected virtual String GetSql(string condition,string orderBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [listid] ,[plistid] ,[orderid] ,[colname] ,[headcn] ,[coltype] ,[isquery] ,[isshow] ,[oname] ,[fmt] ,[editmode] ,[controltype] ,[maxlength] ,[digits] ,[defvalue] ,[iskey] ,[notnull] ,[columnwidth] ,[colspan] ,[columnoption] ,[columnparam] ,[tp1] ,[tp2] ,[tp3] ,[tp4] ,[tp5] ,[remark] ,[tp6] ,[tp7] ,[tp8] ,[tp9] ,[tp10] ");
            strSql.Append(" FROM Sys5a01 ");
 			
			if (!string.IsNullOrEmpty(condition)) strSql.Append(" where " + condition);				
			if(!string.IsNullOrEmpty(orderBy)) strSql.Append( " order by " + orderBy );
			
            return  strSql.ToString();
        }

        /// <summary>
        /// 根据条件和参数得到Sys5a01对象
        /// </summary>
        /// <param name="Condition">Sql条件</param>
        /// <param name="hs">参数和值</param>
        /// <returns></returns>
        public virtual Model.Sys5a01 GetModel(String Condition, Hashtable hs)
        {
			String sql = GetSql(Condition,String.Empty);
			DataTable dt = DBHelper.GetTableByCondition(sql,  hs);
			return (dt.Rows.Count > 0) ? Model.Sys5a01.GetSys5a01ListByDataTable(dt)[0] : null;
        }

        /// <summary>
        /// 根据主键的值和某字段得到其值,可以用来判断是否存在
        /// </summary>
        /// <param name="TableKeyValue">主键值</param>
        /// <param name="FieldName">要查询的字段名称</param>
        /// <returns></returns>
        public virtual Object GetValueByField(Object TableKeyValue, Object FieldName)
        {
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select " + FieldName + " from Sys5a01 ");
			strSql.Append(" where listid=@TableKeyValue ");
			SqlParameter p = new SqlParameter("@TableKeyValue", TableKeyValue);

			return DBHelper.ExecuteScalar(strSql.ToString(), p);
        }
		
        /// <summary>
        /// 分页SQL
        /// </summary>
        /// <param name="page"></param>
        /// <returns></returns>
        public virtual ReturnPageData GetList(Pager page)
        {
            ReturnPageData data = new ReturnPageData();
            //page.PageIndex++;
            page.PrimaryKey = "listid ";

            page.ShowFields = " [listid] ,[plistid] ,[orderid] ,[colname] ,[headcn] ,[coltype] ,[isquery] ,[isshow] ,[oname] ,[fmt] ,[editmode] ,[controltype] ,[maxlength] ,[digits] ,[defvalue] ,[iskey] ,[notnull] ,[columnwidth] ,[colspan] ,[columnoption] ,[columnparam] ,[tp1] ,[tp2] ,[tp3] ,[tp4] ,[tp5] ,[remark] ,[tp6] ,[tp7] ,[tp8] ,[tp9] ,[tp10] ";

            page.TableName = " Sys5a01 ";

			page.StrWhere = string.IsNullOrEmpty(page.StrWhere) ? " 1=1 " : page.StrWhere;
			
            page.StrWhere = string.IsNullOrEmpty(page.AutoWhere) ? page.StrWhere : page.StrWhere + " And ("+ page.AutoWhere +")";

            page.For = PagerFor.Count;
            DataTable dtRowCount = DBHelper.GetSelectLimit(page);
            if (dtRowCount == null || dtRowCount.Rows.Count == 0)
            {
                data.AllRowCount = 0;
                return data; //NULL DATA
            }
            data.AllRowCount = Convert.ToInt32(dtRowCount.Rows[0]["Total"]);
            page.For = PagerFor.Data;

            DataTable dtPageData = DBHelper.GetSelectLimit(page);
            data.CurrnetPageData = dtPageData;

            //汇总
            page.For = PagerFor.Summary;
            data.SummaryData = DBHelper.GetSelectLimit(page);
            
            return data;
        }		
		
		public virtual DataTable GetList(string c,Hashtable hs)
		{
			string sql = GetSql(c,null); //得到SQL语句
			//返回结果集
			return DBHelper.GetData(sql,DBHelper.GetListByHashtable(hs).ToArray());
		}
    }
}
